import sqlite3
import os
from typing import List, Dict, Optional
from config import DATABASE_PATH

class ArbitrationDB:
    def __init__(self):
        self.db_path = DATABASE_PATH
        self.init_database()
    
    def init_database(self):
        """初始化数据库表"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 创建仲裁案件表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS arbitration_cases (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                case_number TEXT UNIQUE NOT NULL,
                title TEXT NOT NULL,
                status TEXT NOT NULL,
                plaintiff TEXT NOT NULL,
                defendant TEXT NOT NULL,
                amount REAL,
                created_date TEXT,
                updated_date TEXT,
                description TEXT
            )
        ''')
        
        # 插入示例数据
        sample_cases = [
            ("ARB2024001", "商务合同纠纷", "进行中", "公司A", "公司B", 100000.00, "2024-01-15", "2024-01-20", "关于软件开发合同的争议"),
            ("ARB2024002", "劳动争议", "已结案", "员工张三", "某公司", 50000.00, "2024-02-01", "2024-02-15", "关于加班费的争议"),
            ("ARB2024003", "货物买卖纠纷", "可用", "贸易公司C", "制造商D", 200000.00, "2024-03-01", "2024-03-01", "关于货物质量的争议"),
        ]
        
        cursor.executemany('''
            INSERT OR IGNORE INTO arbitration_cases 
            (case_number, title, status, plaintiff, defendant, amount, created_date, updated_date, description)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', sample_cases)
        
        conn.commit()
        conn.close()
    
    def query_cases(self, status: Optional[str] = None) -> List[Dict]:
        """查询仲裁案件"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        if status:
            cursor.execute('''
                SELECT * FROM arbitration_cases WHERE status = ?
            ''', (status,))
        else:
            cursor.execute('SELECT * FROM arbitration_cases')
        
        rows = cursor.fetchall()
        conn.close()
        
        # 转换为字典格式
        columns = ['id', 'case_number', 'title', 'status', 'plaintiff', 'defendant', 'amount', 'created_date', 'updated_date', 'description']
        return [dict(zip(columns, row)) for row in rows]
    
    def get_available_cases(self) -> List[Dict]:
        """获取可用的案件"""
        return self.query_cases(status="可用")
    
    def search_cases(self, keyword: str) -> List[Dict]:
        """搜索案件"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT * FROM arbitration_cases 
            WHERE title LIKE ? OR description LIKE ? OR case_number LIKE ?
        ''', (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%'))
        
        rows = cursor.fetchall()
        conn.close()
        
        columns = ['id', 'case_number', 'title', 'status', 'plaintiff', 'defendant', 'amount', 'created_date', 'updated_date', 'description']
        return [dict(zip(columns, row)) for row in rows]
